In order to learn how to use CockroachDB with a JPA provider like Hibernate, let’s build a Spring Boot application that stores details of video game leaderboards in a CockroachDB database hosted on the CockroachDB Serverless platform.
This Spring Boot application will contain two HTML pages and will be deployed to the cloud using Heroku.
If you want to jump right into the codebase for the Spring Boot application, you can find it in the GitHub repository.
Before we create the application
Before jumping into creating the application, take a moment to confirm that we have:
Access to a CockroachDB Serverless account. We can create an account using CockroachDB’s serverless platform.
The CockroachDB CLI installed on your computer. The installation guide explains how to install the CLI across Windows, macOS, and Linux operating systems.
A basic understanding of the Java programming language and that we have configured the Java environment on your computer
The Spring Boot and Maven CLIs installed on our computers
Node.js on our computers
The Git and GitHub Official command-line tools
The Heroku CLI
How to prepare the application resources
Our leaderboard application stores and retrieves its data from a Cockroach database using the CockroachDB Serverless platform.
To prepare the application resources, we’ll retrieve the connection details for a CockroachDB cluster. Using a computer terminal, we’ll connect to the cluster and create a database for the leaderboard.
Retrieve the database connection info on CockroachDB Serverless
If you’re already a CockroachDB user with a cluster, open the cluster and click the Connect button on the Cluster Overview page. This will open the Connect window.
If you’re a new CockroachDB user, create an account and follow the onboarding prompts to create a free Serverless cluster in beta mode.
You’ll then be asked to create an SQL user. Copy and save your password in a secure location.
Click Next to view the Connect window. Here, you can find connection information, including your username, host, database, and port.
How to establish a cluster connection
From the Connect window, select General connection string under Select option/language. An operating system will be automatically detected, but we may choose another operating system via the dropdown menu.
The commands we’re instructed to run will depend on which operating system we’re using.
The next command within the modal will be to connect from our command line to the created cluster using the installed CockroachDB Client. Select CockroachDB Client under Select option/language.
After executing the command, the cluster details will be displayed in your console.
Create a Database
At this point, we have an active Cockroach shell that creates a connection between the computer and the CockroachDB cluster. We’ll now proceed to execute SQL statements through the running shell to create a database and model it to store data for the leaderboard application.
The following steps outline the SQL statements needed to create a database within our CockroachDB cluster.
Begin by executing the command below to create a database named leaderboard:
CREATE DATABASE leaderboard;
Next, execute the USE command to set the default cluster database to the leaderboard database you created. The command will cause subsequent SQL statements to be executed on the leaderboard database.
USE leaderboard;
Execute the SQL statement below to create a table in the database named leaderboard_entries.
CREATE TABLE leaderboard_entries (
id INT DEFAULT unique_rowid(),
game_name VARCHAR(200) NOT NULL,
game_score INT DEFAULT 0,
player_username VARCHAR(200) NOT NULL,
team_size INT DEFAULT 1,
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now()
);
The table created will store the details of each leaderboard record. When a record is inserted, the unique_rowid() function will generate a unique number as the value of the id field.
Now, we’ll create a Spring Boot project to use the database hosted on Cockroach Serverless. The Spring Boot project will store and retrieve data from the leaderboard database.
Bootstrapping a Spring Boot Project
Spring Boot is a framework that reduces the amount of configuration needed to bootstrap a production-ready Spring project.
The steps below will guide you through building a Spring Boot project using the Spring Boot Command-Line Interface (CLI). Alternatively, you can use the Spring-initialzr.
To get started, generate a Spring Boot boilerplate for the web, named leaderboard, in a new directory and go into the directory:
spring init --dependencies=web leaderboard
cd leaderboard
Then, build and run the boilerplate application using the installed Maven CLI:
mvn spring-boot:run
When started, the Spring Boot application will be available for viewing through our local browser at http://localhost:8080. When opened through our web browser, a 404 error message will be displayed, as no route within the application has been created. We’ll create two routes in the later parts of this tutorial.
Install the project dependencies
Now that we’ve generated a Spring Boot application, we’ll install all dependencies needed for building the leaderboard application.
To begin, open the leaderboard project in an integrated development environment (IDE) for Java.
Open the pom.xml file within the leaderboard project and add the code below into the dependencies element:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-loader</artifactId>
</dependency>
The above code adds the following dependencies to the Spring Boot project:
The Postgres JDBC driver for connecting to the Cockroach database
The Hibernate-Core for using Hibernate as a JPA provider
The Thymeleaf Java template engine for rendering views and data in the HTML pages within the Spring Boot application
Next, stop the running Spring Boot application and execute the Maven command below to install the new dependencies that were added to the pom.xml file.
mvn clean install -DskipTests
Note: The -DskipTests flag will cause Maven to skip running the boilerplate test suites generated for the project.
Add database credentials
Now it’s time to add the connection details of the database cluster we created on CockroachDB Serverless.
One of the benefits of Spring Boot is the minimal configuration it requires to use Hibernate as a Java Persistence API (JPA) provider. You only need to include it in your pom.xml file and specify the database connection details.
We’ll use the steps below to specify the JDBC URL for your Cockroach database and also specify the CockroachDB dialect for Hibernate to use.
The Connect modal within the Cockroach Serverless Platform provides the feature to format a JDBC URL to be used as the HOST_URL in the application.properties file.
Navigate to the CockroachDB Serverless platform and reload it to view the leaderboard database that was recently created, then click the Connect button to launch the Connect modal.
To format the JDBC URL, click the Database dropdown and select the leaderboard database.
Next, click the Select option/language dropdown and select the Java option.
As highlighted at the lower parts of the image below, copy only the parts of the JDBC URL without the sslrootcert option as we won’t be using a certificate authority (CA) file.
Open the application.properties file within the leaderboard/src/main/resources directory. This file will specify the database connection details and a few other configurations, including the CockroachDB dialect for Hibernate.
Add the code block below into the application.properties file. Be sure to replace the HOST_URL, placeholder below with the JDBC URL obtained above and also replace the “Enter password secret” placeholder with our cluster password.
spring.datasource.url=jdbc:HOST_URL
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.CockroachDB201Dialect
spring.jpa.show-sql=true
spring.datasource.dbcp2.test-while-idle=true
spring.datasource.dbcp2.validation-query=select 1
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
How to build the leaderboard application
A Spring Boot application consists of a controller, repository class, service layer, and domain. We’ll create four packages within the src/main/java/com/example/leaderboard directory to contain these components.
Create the first directory named entities and a Leaderboard.java file within it. Then, add the code below into the entities/Leaderboard.java file to build the application’s domain layer and model the Cockroach database.
package com.example.leaderboard.entities;
import javax.persistence.*;
@Entity
@Table(name="leaderboard_entries")
public class Leaderboard {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "game_name")
private String gameName;
@Column(name = "game_score")
private String gameScore;
@Column(name = "player_username")
private String playerUsername;
@Column(name = "team_size")
private String teamSize;
@Column(name = "created_at")
private String createdAt;
// setters
public void setId(Long id) {
this.id = id;
}
public void setGameName(String gameName) {
this.gameName = gameName;
}
public void setPlayerUsername(String playerUsername) {
this.playerUsername = playerUsername;
}
public void setTeamSize(String teamSize) {
this.teamSize = teamSize;
}
public void setCreatedAt(String createdAt) {
this.createdAt = createdAt;
}
public void setGameScore(String gameScore) {
this.gameScore = gameScore;
}
// getters
public Long getId() {
return id;
}
public String getGameScore() {
return gameScore;
}
public String getGameName() {
return gameName;
}
public String getPlayerUsername() {
return playerUsername;
}
public String getTeamSize() {
return teamSize;
}
public String getCreatedAt() {
return createdAt;
}
}
The class above uses several accessors to build a model of the leaderboard_entries table within the Cockroach database.
Next, create the second package called repositories within the src/main/java/com/example/leaderboard directory. Then, create a LeaderboardRepository.java file in the repositories package.
Add the code below into the LeaderboardRepository.java file to create an interface that extends the JpaRepository interface. You will use methods inherited from the JpaRepository interface to perform CRUD operations while interacting with the Cockroach database.
package com.example.leaderboard.repositories;
import com.example.leaderboard.entities.Leaderboard;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface LeaderboardRepository extends JpaRepository<Leaderboard, Long> { }
Create the third package named services within the src/main/java/com/example/leaderboard directory.
Within the new services package, create a LeaderboardService.java file to store the application’s business logic. This will then use the LeaderboardRepository interface through Spring’s @Autowired annotation.
Now, add the code below into the LeaderboardService.java file to build the application’s business logic for adding leaderboard details into the database.
package com.example.leaderboard.services;
import com.example.leaderboard.entities.Leaderboard;
import com.example.leaderboard.repositories.LeaderboardRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.stereotype.Service;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import java.util.List;
@Service
@Configurable
public class LeaderboardService {
@Autowired
LeaderboardRepository leaderboardRepository;
@PersistenceContext
private EntityManager entityManager;
@Transactional
public void insertLeaderboard(Leaderboard leaderboardData) {
entityManager.createNativeQuery(
"INSERT INTO leaderboard_entries (game_name, game_score, player_username, team_size) VALUES (?,?,?,?)")
.setParameter(1, leaderboardData.getGameName())
.setParameter(2, leaderboardData.getGameScore())
.setParameter(3, leaderboardData.getPlayerUsername())
.setParameter(4, leaderboardData.getTeamSize())
.executeUpdate();
}
public LeaderboardService() {}
public List<Leaderboard> getLeaderboard() {
return leaderboardRepository.findAll();
}
}
Reading through the code above, we will observe that the getLeaderboardEntries method is responsible for retrieving all rows within the leaderboard_entries table, while the insertLeaderboard method inserts new leaderboard records into the database.
Lastly, create the last package called controller within the /src/main/java/com/example/leaderboard directory. Then, create a LeaderboardController.java file within the controller package.
The code within the LeaderboardController.java file will be used to manage the API requests made by a user through a web browser. We’ll use Thymeleaf to respond with HTML content when a user makes a request to either the default ( /) or /create-entry endpoints.
Add the code block below into the LeaderboardController.java file:
package com.example.leaderboard.controller;
import com.example.leaderboard.entities.Leaderboard;
import com.example.leaderboard.services.LeaderboardService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.ui.Model;
@Controller
public class LeaderboardController {
@Autowired
LeaderboardService leaderboardService;
@GetMapping("/")
public String index(Model model) {
model.addAttribute("entries", leaderboardService.getLeaderboard());
return "home";
}
@GetMapping("/status")
public String status() {
return "status";
}
@GetMapping("/create-entry")
public String getEntries(@ModelAttribute Leaderboard leaderboard, Model model) {
model.addAttribute("leaderboard", new Leaderboard());
return "create-entry";
}
@PostMapping("/create-entry")
public String submitEntryData(Leaderboard leaderboardData, Model model) {
model.addAttribute("leaderboard", leaderboardData);
leaderboardService.insertLeaderboard(leaderboardData);
return "redirect:/";
}
}
At this point, the src/main/java/com/example/leaderboard directory should contain the four directories and five files that you just created.
Create the application views
Thymeleaf is a modern Java template engine with significant support for Spring web applications. You’ll use the Standard Expression Syntax from Thymeleaf to format and display the details of each leaderboard in your application.
In the previous steps, we added Thymeleaf to the application by installing the Thymeleaf dependency. The LeaderboardController also contains the URL mappings and the views to display.
Next, we’ll create two HTML files and one CSS file within the src/main/resources directory of the application.
Create a home.html file in the src/main/resources/templates directory to be rendered as the default page of the Spring Leaderboard application.
Next, add the HTML code below to the home.html file:
<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Getting Started: Serving Web Content</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<link th:href="@{/css/main.css}" rel="stylesheet">
</head>
<body>
<nav class="flex">
<p> All Leaderboard Entries </p>
<button>
<a href="/create-entry">
Create New Entry
</a>
</button>
</nav>
<div class="container">
<div class="content">
<table style="width: 100%; text-align: center;">
<tr style="border-bottom: 1px solid #000">
<th>Game Name</th>
<th>Game Score</th>
<th>Player Name</th>
<th>Team Size</th>
<th>Created</th>
</tr>
<tr th:each="leaderboard: ${entries}">
<td th:text="${leaderboard.gameName}"></td>
<td th:text="${leaderboard.gameScore}"></td>
<td th:text="${leaderboard.playerUsername}"></td>
<td th:text="${leaderboard.teamSize}"></td>
<td th:text="${leaderboard.createdAt}"></td>
</tr>
</table>
</div>
</div>
</body>
</html>
The HTML code above will render all leaderboards from the getLeaderboardEntries method used in the index controller. An iteration is further performed on the leaderboards using the th:each expression from Thymeleaf to display each leaderboard in a table.
Next, we’ll make a form that users will complete to add a leaderboard entry. Create a create-entry.html file in the templates directory using the following code.
<!DOCTYPE HTML>
<html>
<head>
<title>CockroachDB Java Leaderboard</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<link th:href="@{/css/main.css}" rel="stylesheet">
</head>
<body>
<nav class="flex">
<p> All Entries </p>
<button>
<a href="/">
View All Leaderboards
</a>
</button>
</nav>
<div class="container">
<form th:action="@{/create-entry}" th:object="${leaderboard}" method="post">
<h2 class="align-center"> Create New Leaderboard </h2>
<hr/>
<br/>
<div>
<label for="leaderboardName">
Leaderboard Name
</label>
<br/>
<input
type="text"
th:field="*{gameName}"
id="leaderboardName"
placeholder="Leaderboard Name"
>
<br/>
</div>
<div>
<label for="username">
Player Username
</label>
<br/>
<input
type="text"
th:field="*{playerUsername}"
id="username"
placeholder="Your Player Username"
>
</div>
<div>
<label for="teamSize">
Team Size
</label>
<br/>
<input
type="number"
th:field="*{teamSize}"
id="teamSize"
placeholder="Your Team Size"
>
</div>
<div>
<label for="gameScore">
Game Score
</label>
<br/>
<input
type="number"
th:field="*{gameScore}"
id="gameScore"
placeholder="Your Game Score"
>
</div>
<br/>
<button type="submit">Create Leaderboard</button>
</form>
</div>
</body>
</html>
The th:action="@{/create-entry}" expression directs the form element to submit the user’s input to the /create-entry endpoint. The gameName, playerUsername, teamSize, and gameScore fields match the fields from the Leaderboard object that you created in the getEntries controller.
Next, create a directory named css within the /src/main/resources/static directory. Proceed to create a stylesheet file named main.css file within the static/css directory. The purpose of the CSS file is to style the default and /create-entry pages.
Add the CSS code below into the main.css file:
.flex {
display: flex;
justify-content: space-between;
}
.align-center {
text-align: center;
}
nav {
border-bottom: 1px solid grey;
padding: 0 1rem;
}
form {
display: flex;
flex-direction: column;
}
input {
margin: .5rem 0;
height: 35px;
width: 95%;
border: 1px solid #000;
border-radius: 5px;
padding: .5rem .5rem;
color: #000;
}
.container {
display: flex;
justify-content: center;
align-items: center;
background-color: #F5F7FA;
height: calc(100vh - 50px);
}
.content {
width: 940px;
height: calc(100vh - 100px);
background: white;
padding: 1rem;
border-radius: 10px;
}
tr, th, td {
padding: 20px;
}
tr:nth-child(even) {
background-color: #D6EEEE;
}
tr {
border-bottom: 5px solid #ddd;
}
form {
background-color: #fff;
border-radius: 5px;
box-shadow: 0 2px 3px grey;
width: 30rem;
padding: 1rem;
}
button {
height: 40px;
border: 1px solid grey;
border-radius: 5px;
color: #000;
}
button:hover {
cursor: pointer;
}
a {
text-decoration: none;
}
How to deploy the leaderboard application to Heroku
Heroku is a cloud-based platform-as-a-service (PaaS) used for deploying web applications and API services written in various programming languages.
For Java applications, Heroku provides the buildpack, which uses Maven to download the dependencies and build the application, and OpenJDK to run the application.
Using heroku-buildpack-java will compile and deploy the leaderboard project from a local git repository created using the Git CLI.
Before you begin the deployment process with Heroku, open the pom.xml file within the leaderboard project to change the project’s Java version to a value compatible with Heroku.
Replace the java.version element with the code below:
<java.version>1.8</java.version>
Create local and remote repositories
Git is a CLI-based tool for tracking changes, while GitHub is a cloud-based service for storing project-related files in a repository. For this demonstration, we’ll create a local and remote git repository using the Git and GitHub CLI tools.
Execute the command below to initialize a local git repository within the leaderboard project:
git init
Next, execute the command below to create a .gitignore file containing the target folder.
echo "target" > .gitignore
Now, execute the add command to add all files within the leaderboard project to the created local repository.
git add .
Using the command below, we’ll commit the last change that you made to the leaderboard local repository.
git commit -m “feat: built an MVP of the leaderboard project”
Using the GitHub Official (gh) CLI tool, we’ll create a remote repository to store the leaderboard project files in the cloud.
When used for the first time, the command will prompt you to authenticate either using our GitHub Personal Access Token or via a browser window.
Execute the gh command below to create a public repository to store the leaderboard codebase. Replace the GITHUB_USERNAME placeholder with your GitHub account username to ensure the repository we are creating has a unique name.
gh repo create <GITHUB_USERNAME>-spring-leaderboard --public
Execute the command below to specify the URL of the repository created as the Spring-leaderboard remote origin:
git remote add origin https://github.com/<GITHUB_USERNAME>/<GITHUB_USERNAME>-spring-leaderboard.git
Create a Heroku application
To begin, we’ll create an application on Heroku using the computer terminal. The Heroku application will act as a container for storing the leaderboard application that we’ll deploy in later steps.
Execute the command below to create a Heroku application. If we’re not authenticated, Heroku will prompt us to press a key to authenticate through our web browser.
heroku create
3. Next, execute the command below to push the codebase for the leaderboard project to Heroku. Heroku will start the build process using Maven immediately after the files are pushed.
git push heroku main
4. After the application is deployed to Heroku, the deployment URL will be printed out in the Heroku deployment logs on your computer. You can see the highlighted deployment URL in the image below of the deployed leaderboard application.
Based on the configuration of the leaderboard project, Heroku will automatically create and use a Postgres database addon to make the deployment process easier. In this scenario, a database addon from Heroku is not needed as we are using CockroachDB.
5. Execute the addon command below to destroy the database addon.
heroku addons:destroy heroku-postgresql
Testing the deployed application
Using a web browser, navigate to the deployment URL of the leaderboard application to create our first leaderboard.
On the default page, we’ll find leaderboard headings without any list entries.
Click the Create New Entry button to navigate to the create-entry page. We’ll input details for our first leaderboard on this page.
2. On the create-entry page, add the leaderboard name, player username, team score, and game score detail into the input fields in the form.
Next, click the Create Leaderboard button to submit the inputted details.
3. After clicking the button, the details will be saved to the database and the application will redirect to the default page that lists all created leaderboard entries.
You can also see the leaderboard details by querying the database through an active Cockroach shell:
And with that, our leaderboard application is fully functional and stores data in CockroachDB.
Conclusion
Congratulations on completing this tutorial! We’ve built a leaderboard application that stores data in a CockroachDB database contained within a cluster on CockroachDB Serverless.
We began by retrieving the connection details of a CockroachDB cluster from CockroachDB Serverless, then used the CockroachDB client from a local terminal to connect to the cluster and create a database. Next, we used the Spring CLI to bootstrap a boilerplate Spring Boot web application while using Maven to install the needed dependencies. To test the completed leaderboard application, we deployed it using the Heroku CLI. This enabled us to insert and view details of a sample leaderboard.
CockroachDB Serverless greatly simplified the process of creating a cluster for our application. While building the leaderboard application locally, we can achieve the best experience setting up your CockroachDB cluster by using CockroachDB Serverless.
